# encoding: utf-8
'''
本程序的作用是查询任何时间段、任何教室在上什么课
每学期开始的时候修改 DBManagement 类的 __init__ 方法里面的 course_term
# 新的学期删除旧的学期的 dynamic_courses 数据，course_term
  '''

import json
import traceback
import pymysql

CLASSROOMS = {'主楼': ['323', '603'],
              '信息楼': ['112', '113', '201', '215', '721', '724', '824', '921', '924', 'B203', 'B301', 'B303', 'B304',
                         'B315', 'B316', 'B404', 'B415', 'B416', 'B617', 'B619', 'B721', 'B821', 'B824'],
              '冶金楼': ['110', '201', '202', '203', '205', '206', '209', '210'],
              '土木楼': ['1018', '201', '202', '205', '301', '302', '402', '818', '918'],
              '外语楼': ['107', '108', '113', '115', '201', '218', '302', '304', '305'],
              '实验楼': ['101', '102'],
              '工程训练中心(西区)': ['302', '307'],
              '教学楼': ['101', '102', '103', '105', '107', '109', '111', '201', '202', '203', '211', '215', '217',
                         '301', '302', '303', '305', '309', '313', '315', '401', '402', '403', '405', '407', '409',
                         '502', '503', '505', '507', '509'],
              '文法楼': ['102', '108', '301', '304', '305', '401', '402'],
              '昌平南楼': ['S301', 'S302', 'S303', 'S304', 'S305', 'S306', 'S308', 'S309', 'S310', 'S311', 'S312',
                           'S313', 'S401'],
              '机械楼': ['106', '1105', '1205', '214', '314', '414', '517', '616', '720', '820', '920'],
              '测试楼': ['301', '304', '305', '315'],
              '理化楼': ['308'],
              '理学楼': ['211'],
              '科技楼': ['601'],
              '管庄教学楼': ['201', '203', '204', '211', '213', '216', '222', '228', '303', '305', '308', '309', '312',
                             '314', '316', '328', '401'],
              '管理楼': ['201', '209', '301', '303', '306', '313', '404', '405', '502', '504', '505', '506', '507',
                         '508', '510', '512', '602', '609', '703', '707'],
              '逸夫楼': ['101', '102', '104', '105', '107', '201', '202', '204', '205', '206', '207', '301', '302',
                         '303', '304', '305', '306', '307', '401', '402', '403', '404', '405', '406', '407', '501',
                         '502', '503', '504', '505', '506', '507', '601', '602', '603', '604', '605', '606', '607',
                         '701',
                         '702', '703', '705', '706', '707', '805', '806'],
              '金物楼': ['323', '329']}


class DBManagement:
    def __init__(self, course_term="2024-2025-2"):
        # 每学期开始的时候需要更新学期
        # self.course_term = "2023-2024-2"
        self.course_term = course_term
        self.DB_HOST = '127.0.0.1'
        self.DB_PORT = 3306

        self.DB_USERNAME = 'schedule'
        self.DB_PASSWORD = 'f9T,Hh6i%b*D'
        self.DB_DATABASE = 'schedule'
        # self.DB_USERNAME = "root"  # 'schedule'
        # self.DB_PASSWORD = "zhao"  # 'f9T,Hh6i%b*D'
        # self.DB_DATABASE = "courseQuery"  # 'schedule'

        self.DB_CHAREST = 'utf8mb4'
        self.connection = pymysql.connect(
            host=self.DB_HOST,
            port=self.DB_PORT,
            user=self.DB_USERNAME,
            passwd=self.DB_PASSWORD,
            db=self.DB_DATABASE,
            charset=self.DB_CHAREST
        )

    # 判断是否存在指定的数据表
    def __check_table_exists(self, table_name):
        try:
            with self.connection.cursor() as cursor:
                # 查询数据表是否存在
                cursor.execute(f"SHOW TABLES LIKE '{table_name}'")
                return bool(cursor.fetchone())
        except Exception as error:
            traceback.print_exc()

    # 判断是否存在存放 课程动态信息的表 dynamic_courses ，没有则创建
    def create_dynamic_courses_table(self):
        with self.connection.cursor() as cursor:
            # 查询数据表是否存在
            cursor.execute("SHOW TABLES LIKE 'dynamic_courses'")
            result = cursor.fetchone()
            # 如果数据表不存在，则创建一个新的数据表
            if not result:
                sql = f"""
                CREATE TABLE dynamic_courses (
                    `id` INT AUTO_INCREMENT PRIMARY KEY,
                    `course_term` VARCHAR(20) DEFAULT '{self.course_term}',
                    `week` TINYINT,
                    `day` TINYINT,
                    `order` TINYINT,
                    `budding` VARCHAR(40),
                    `detail` JSON
                )
                """
                cursor.execute(sql)
                self.connection.commit()
                print("成功创建数据表 dynamic_courses")

    # 检查输入的参数是否有效
    def check_paramers(self, WEEK, DAY, ORDER, budding: str):
        try:
            if not (0 <= int(WEEK) < 16):
                return False, "星期参数不正确"
            elif not (0 <= int(DAY) < 7):
                return False, "日期参数不正确"
            elif not (0 <= int(ORDER) < 6):
                return False, "上课时间段参数不正确"
            elif not budding in list(CLASSROOMS.keys()):
                return False, "上课楼宇参数不正确"
            return True, "参数校验通过"
        except:
            return False, "出错了:"

    # 获取一门课程的选选课人数，去course_properties里面关联查询
    def __getStudentNumberByCourseId(self, id):
        with self.connection.cursor(pymysql.cursors.DictCursor) as cursor:
            sql_select = f"SELECT count(*) FROM `course_properties` WHERE course_id = '%s'"
            cursor.execute(sql_select, (id))
            result = cursor.fetchone()
            return result.get("count(*)", 0)

    # 从courses表中查询在指定时间地点上课的所有课程信息
    def __getCoursesByTimeAndPlace(self, WEEK: int, ORDER: int, DAY: int, budding: str):
        with self.connection.cursor(pymysql.cursors.DictCursor) as cursor:
            # 处理MySQL的特殊字符，防止sql出问题
            day = f'\\"day\\": {DAY}'  # 星期 0-6
            order = f'\\"order\\": {ORDER}'  # 第几节课 0-5
            resData = []
            course_count = 0  # 记录符合条件的课程数量
            for classroom in CLASSROOMS[budding]:
                sql = f"""
                    SELECT id, course_id, name, course_serial, course_number, teachers, summary, type, periods
                    FROM (
                        SELECT id, course_id, name, course_serial, course_number, teachers, summary, type, periods,
                               ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) as rn
                        FROM courses
                        WHERE periods LIKE "%{budding}%" 
                          AND periods LIKE "%{classroom}%"
                          AND periods LIKE "%{order}%" 
                          AND periods LIKE "%{day}%"
                          AND course_term = "{self.course_term}"
                    ) subquery
                    WHERE rn = 1
                    """
                cursor.execute(sql)
                result = cursor.fetchall()
                if len(result) > 0:
                    for i in range(len(result)):
                        periods = json.loads(result[i]["periods"])
                        flag = False  # 判断是否符合week信息
                        for item in periods:
                            if item["day"] == int(DAY) and item["order"] == ORDER and WEEK in item["weeks"] and item[
                                "building"] == budding and item["classroom"] == classroom:
                                flag = True
                                break
                        if flag:
                            # 根据选课人数处理时间地点冲突的课程，如果冲突，保留选课人数多的课程
                            # 与上一个对比
                            if course_count > 0 and classroom == resData[course_count - 1]["classroom"]:
                                # 根据 course 的 id 判断选课的人数，保留选课人数多的课
                                preNum = self.__getStudentNumberByCourseId(resData[course_count - 1]["id"])
                                curNum = self.__getStudentNumberByCourseId(result[i]["id"])
                                print("教室发生了冲突：", classroom)
                                print(resData[course_count - 1]["id"], resData[course_count - 1]["name"], "选课人数:",
                                      preNum)
                                print(result[i]["id"], result[i]["name"], "选课人数:", curNum)
                                if preNum > curNum:
                                    # 忽略，不添加
                                    continue
                                else:
                                    # 删除前面的，添加新的
                                    resData.pop()
                                    course_count -= 1
                            resData.append({
                                "id": result[i]["id"],
                                "course_id": result[i]["course_id"],
                                "course_number": result[i]["course_number"],
                                "course_serial": result[i]["course_serial"],
                                "name": result[i]["name"],
                                "teachers": json.loads(result[i]["teachers"]),
                                "type": result[i]["type"],
                                "budding": budding,
                                "classroom": classroom,
                                "summary": result[i]["summary"],
                                "periods": json.loads(result[i]["periods"]),
                            })
                            course_count += 1  # 记录符合条件的课程数量
            # 获取数据的循环结束
            cursor.close()
            # 返回数据
            return resData

    # 向课程动态表中增加数据
    def __create_or_addDynamicCourses(self, WEEK: int, ORDER: int, DAY: int, BUDDING: str, refresh=False):
        with self.connection.cursor() as cursor:
            detail = self.__getCoursesByTimeAndPlace(WEEK=WEEK, ORDER=ORDER, DAY=DAY, budding=BUDDING)
            # 首先按照WEEK, ORDER, DAY, BUDDING查询数据
            sql_select = """
                SELECT * FROM dynamic_courses 
                WHERE `course_term` = %s AND `week` = %s AND `order` = %s AND `day` = %s AND `budding` = %s
                """
            cursor.execute(sql_select, (self.course_term, WEEK, ORDER, DAY, BUDDING))
            result = cursor.fetchone()
            if result:
                # 强制更新数据，先删除
                if refresh:
                    self.connection.begin()  # 开启事务
                    # 删除符合条件的数据
                    sql = "DELETE FROM dynamic_courses WHERE `course_term` = %s AND week=%s AND day=%s AND `order`=%s AND budding=%s"
                    cursor.execute(sql, (self.course_term, WEEK, DAY, ORDER, BUDDING))
                    self.connection.commit()
                    # if cursor.rowcount > 0:  # 获取删除的行数
                    #     print("Data deleted successfully")
                    # else:
                    #     print("不存在数据")
                else:
                    sql_update = """
                        UPDATE dynamic_courses 
                        SET `detail` = %s 
                        WHERE `course_term` = %s AND `week` = %s AND `order` = %s AND `day` = %s AND `budding` = %s
                        """
                    self.connection.begin()  # 开启事务
                    cursor.execute(sql_update, (json.dumps(detail), self.course_term, WEEK, ORDER, DAY, BUDDING))
                    # print("修改数据成功")
                    self.connection.commit()
                    return
            # 如果不存在符合条件的数据，则插入新数据
            sql_insert = """
                INSERT INTO dynamic_courses (`course_term`,`week`, `order`, `day`, `budding`, `detail`) 
                VALUES (%s,%s, %s, %s, %s, %s)
                """
            self.connection.begin()
            cursor.execute(sql_insert, (self.course_term, WEEK, ORDER, DAY, BUDDING, json.dumps(detail)))
            self.connection.commit()  # 提交事务
            # print ("新增数据成功")

    # 搜索动态课程信息
    def __searchDynamicCourses(self, WEEK: int, DAY: int, ORDER: int, budding: str):
        with self.connection.cursor() as cursor:
            # 查询一条符合条件的数据
            sql = "SELECT detail FROM dynamic_courses WHERE course_term=%s AND week=%s AND day=%s AND `order`=%s AND budding=%s LIMIT 1"
            cursor.execute(sql, (self.course_term, WEEK, DAY, ORDER, budding))
            row = cursor.fetchone()
            if row:
                return json.loads(row[0])  # 只返回detail字段
            else:
                return {}

    # 查询课程动态信息
    def getClassDynamicInformation(self, WEEK, DAY, ORDER, budding: str, refresh=False):
        try:
            # 检查参数是否正确
            flag, msg = self.check_paramers(WEEK, DAY, ORDER, budding)
            if not flag:
                return False, msg
            if refresh or not self.__searchDynamicCourses(WEEK=int(WEEK), DAY=int(DAY), ORDER=int(ORDER),
                                                          budding=budding):
                # 更新数据
                self.__create_or_addDynamicCourses(WEEK=int(WEEK), DAY=int(DAY), ORDER=int(ORDER), BUDDING=budding,
                                                   refresh=refresh)
            res = self.__searchDynamicCourses(WEEK=int(WEEK), DAY=int(DAY), ORDER=int(ORDER), budding=budding)
            if res:
                return True, res
            return False, "没有查询到数据，可能当前时间点没有正在进行的课程"
        except:
            traceback.print_exc()
            return False, "出错了，"

    # 在对象被销毁时，会自动调用__del__方法，关闭数据库连接。
    def __del__(self):
        self.connection.close()


# 测试
if __name__ == '__main__':
    db = DBManagement()
    db.create_dynamic_courses_table()
    flag, msg = db.getClassDynamicInformation(
        WEEK=0,
        DAY=2,
        ORDER=0,
        budding="逸夫楼",
        refresh=True
    )
    print(msg)


